<?php
class keu_pembayaran_pinjaman_model extends MY_Model {
	var $model = 'keu_pembayaran_pinjaman';
	
	
	function GetJSON()
	{
		$ls_sql	= "	SELECT	PIN.ID_PINJAMAN, PIN.ID_ANGGOTA, AG.PERSON_ID,
							TO_CHAR(DET.TGL_JATUH_TEMPO, 'DD/MM/YYYY') TGL_JATUH_TEMPO, PIN.JML_PINJAMAN, PIN.KETERANGAN,
							P.FULL_NAME NAMA_ANGGOTA, 
							NVL(DET.SISA_TAGIHAN, TOTAL_TAGIHAN) SISA_TAGIHAN,
							DET.PERIODE
					FROM	SIS_PINJAMAN_ANGGOTA PIN 
							INNER JOIN SIS_ANGGOTA AG ON AG.ID_ANGGOTA = PIN.ID_ANGGOTA
							INNER JOIN SW_PERSON P ON AG.PERSON_ID=P.PERSON_ID
							INNER JOIN SIS_PINJAMAN_ANGGOTA_DETAIL DET ON DET.ID_PINJAMAN=PIN.ID_PINJAMAN
					WHERE	
							NVL(PIN.ST_APPROVE,'0')='1' AND
							NVL(PIN.ST_TRANSFER,'0')='1' AND
							NVL(DET.ST_LUNAS,'0')='0' AND
							DET.TGL_JATUH_TEMPO BETWEEN 
							TO_DATE('".date_format3(post('date_from'), 'YYYY-MM-DD')."','YYYY-MM-DD') AND 
							TO_DATE('".date_format3(post('date_to'), 'YYYY-MM-DD')."','YYYY-MM-DD') AND					
							(
								UPPER(PIN.ID_PINJAMAN) LIKE UPPER('%".post('keyword')."%') OR
								UPPER(AG.PERSON_ID) LIKE UPPER('%".post('keyword')."%') OR
								UPPER(PIN.KETERANGAN) LIKE UPPER('%".post('keyword')."%')	 OR
								UPPER(P.FULL_NAME)	LIKE UPPER('%".post('keyword')."%')							
							)
					ORDER BY ".(post('sort')=='' ? 'ID_PINJAMAN' : post('sort'))." ".(post('order')=='' ? 'ASC' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
	
	function Add($arr_data){
		$this->db->insert('KEU_TRANS_KAS_BANK', $arr_data);
	}
	
	function GetSingleView($as_pinjaman, $as_periode)
	{
		$ls_sql	= "		SELECT	PIN.ID_PINJAMAN, PIN.ID_ANGGOTA, AG.PERSON_ID,
							TO_CHAR(DET.TGL_JATUH_TEMPO, 'DD/MM/YYYY') TGL_JATUH_TEMPO, PIN.JML_PINJAMAN, PIN.KETERANGAN,
							P.FULL_NAME NAMA_ANGGOTA, 
							DET.TOTAL_TAGIHAN, 
							DET.JML_TAGIHAN, 
							DET.JML_BUNGA, 
							DET.JML_DENDA, 
							NVL(DET.TOTAL_TAGIHAN,0)-(NVL(DET.SISA_TAGIHAN,0)) SUDAH_BAYAR,
							NVL(DET.SISA_TAGIHAN, DET.TOTAL_TAGIHAN) SISA_TAGIHAN,
							DET.PERIODE
					FROM	SIS_PINJAMAN_ANGGOTA PIN 
							INNER JOIN SIS_ANGGOTA AG ON AG.ID_ANGGOTA = PIN.ID_ANGGOTA
							INNER JOIN SW_PERSON P ON AG.PERSON_ID=P.PERSON_ID
							INNER JOIN SIS_PINJAMAN_ANGGOTA_DETAIL DET ON DET.ID_PINJAMAN=PIN.ID_PINJAMAN
					WHERE	
							NVL(PIN.ST_APPROVE,'0')='1' AND
							NVL(PIN.ST_TRANSFER,'0')='1' AND
							NVL(DET.ST_LUNAS,'0')='0' AND
							PIN.ID_PINJAMAN = '".$as_pinjaman."' AND
							DET.PERIODE='".$as_periode."'";
					
							
		$result	= $this->db->query($ls_sql);
		
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	
	function DeleteSingle($as_code)
	{
		try {
			$this->db->delete('KEU_TRANSAKSI_DETAIL', array('ID_DOK' => $as_code));
			$this->db->delete('KEU_TRANSAKSI', array('ID_DOK' => $as_code));
			
			return '';
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}
	
	
	function GetKurs($as_currency, $adt_eff_date){
		$as_currency = trim($as_currency);
		$result = $this->db->get_where('KEU_KURS', array('KD_VALUTA' => $as_currency, 'TGL_EFEKTIF' => $adt_eff_date));
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			$ls_sql = "SELECT * FROM KEU_KURS WHERE KD_VALUTA = '".$as_currency."' AND  TGL_EFEKTIF = (SELECT MAX(TGL_EFEKTIF) FROM KEU_KURS WHERE KD_VALUTA = '".$as_currency."')";
			$result	= $this->db->query($ls_sql);
			if($result->num_rows() == 1){
				return $result->row_array();
			}else{
				$ls_sql = "SELECT 1 HARGA_BELI, 1 HARGA_JUAL, 1 HARGA_RATA FROM DUAL";
				$result	= $this->db->query($ls_sql);
				return $result->row_array();
			}
		}
	}
	
	
	function COAPinjaman()
	{
		$ls_sql	= "	SELECT	ID_COA VALUE,
							ID_COA||' '||NAMA_COA DISPLAY
					FROM	KEU_COA
					WHERE ID_COA IN (SELECT ID_COA FROM KEU_POOLING_PINJAMAN)
					ORDER BY DISPLAY";
					
		$result	= $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return NULL;
		}
	}
	
	
}
?>